import xlwings as xw

ABC = []
for ch in range(65, 91):
    ABC.append('%c' % ch)

file_names = ['./1/11.xlsx', './1/12.xlsx', './1/13.xlsx']
app = xw.App(visible=False, add_book=False)
# nwb = xw.Book('./1.xlsx')
data = []
col_name = []
for file_name in file_names:
    wb = app.books.open(file_name)
    shell = wb.sheets[0]
    rows, cols = shell.used_range.shape
    for i in range(cols):
        c_name = shell.range('%s1' % ABC[i]).value
        if c_name not in col_name:
            col_name.append(c_name)
    # print(col_name)
    for i in range(2, rows + 1):
        stu = []
        for j in range(cols):
            stu.append(shell.range('%s%d' % (ABC[j], i)).value)
        data.append(stu)
print(data)

wb = xw.Book()
sht = wb.sheets[0]
sht.name = 'stu_info'
for i in range(len(col_name)):
    sht.range('%s1' % ABC[i]).value = col_name[i]

for i in range(len(data)):
    for j in range(len(col_name)):
        sht.range('%s%d' % (ABC[j], i+2)).value = data[i][j]

wb.save('./1.xlsx')
app.quit()
